package com.example.demo.mapper;

import com.example.demo.entity.TeamAssignment;
import org.apache.ibatis.annotations.*;

import java.math.BigDecimal;
import java.util.List;

@Mapper
public interface TeamAssignmentMapper {

    // 基本CRUD
    @Insert("INSERT INTO team_assignments (team_id, submitted_by, score, submitted_at, created_at, " +
            "is_excellent, assignment_id, assignment_file, assignment_status) " +
            "VALUES (#{teamId}, #{submittedBy}, #{score}, #{submittedAt}, NOW(), " +
            "#{isExcellent}, #{assignmentId}, #{assignmentFile}, #{assignmentStatus})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(TeamAssignment teamAssignment);

    @Select("SELECT * FROM team_assignments WHERE id = #{id}")
    @Results({
            @Result(column = "team_id", property = "teamId"),
            @Result(column = "submitted_by", property = "submittedBy"),
            @Result(column = "submitted_at", property = "submittedAt"),
            @Result(column = "created_at", property = "createdAt"),
            @Result(column = "is_excellent", property = "isExcellent"),
            @Result(column = "assignment_id", property = "assignmentId"),
            @Result(column = "assignment_file", property = "assignmentFile"),
            @Result(column = "assignment_status", property = "assignmentStatus")
    })
    TeamAssignment selectById(Long id);

    @Select("SELECT * FROM team_assignments WHERE team_id = #{teamId} ORDER BY created_at DESC")
    @Results({
            @Result(column = "team_id", property = "teamId"),
            @Result(column = "submitted_by", property = "submittedBy"),
            @Result(column = "submitted_at", property = "submittedAt"),
            @Result(column = "created_at", property = "createdAt"),
            @Result(column = "is_excellent", property = "isExcellent"),
            @Result(column = "assignment_id", property = "assignmentId"),
            @Result(column = "assignment_file", property = "assignmentFile"),
            @Result(column = "assignment_status", property = "assignmentStatus")
    })
    List<TeamAssignment> selectByTeamId(Long teamId);

    @Select("SELECT * FROM team_assignments WHERE assignment_id = #{assignmentId} ORDER BY created_at DESC")
    @Results({
            @Result(column = "team_id", property = "teamId"),
            @Result(column = "submitted_by", property = "submittedBy"),
            @Result(column = "submitted_at", property = "submittedAt"),
            @Result(column = "created_at", property = "createdAt"),
            @Result(column = "is_excellent", property = "isExcellent"),
            @Result(column = "assignment_id", property = "assignmentId"),
            @Result(column = "assignment_file", property = "assignmentFile"),
            @Result(column = "assignment_status", property = "assignmentStatus")
    })
    List<TeamAssignment> selectByAssignmentId(String assignmentId);

    @Select("SELECT * FROM team_assignments WHERE submitted_by = #{submittedBy} ORDER BY created_at DESC")
    @Results({
            @Result(column = "team_id", property = "teamId"),
            @Result(column = "submitted_by", property = "submittedBy"),
            @Result(column = "submitted_at", property = "submittedAt"),
            @Result(column = "created_at", property = "createdAt"),
            @Result(column = "is_excellent", property = "isExcellent"),
            @Result(column = "assignment_id", property = "assignmentId"),
            @Result(column = "assignment_file", property = "assignmentFile"),
            @Result(column = "assignment_status", property = "assignmentStatus")
    })
    List<TeamAssignment> selectBySubmittedBy(Long submittedBy);

    @Select("SELECT * FROM team_assignments ORDER BY created_at DESC")
    @Results({
            @Result(column = "team_id", property = "teamId"),
            @Result(column = "submitted_by", property = "submittedBy"),
            @Result(column = "submitted_at", property = "submittedAt"),
            @Result(column = "created_at", property = "createdAt"),
            @Result(column = "is_excellent", property = "isExcellent"),
            @Result(column = "assignment_id", property = "assignmentId"),
            @Result(column = "assignment_file", property = "assignmentFile"),
            @Result(column = "assignment_status", property = "assignmentStatus")
    })
    List<TeamAssignment> selectAll();

    @Update("UPDATE team_assignments SET " +
            "team_id = #{teamId}, " +
            "submitted_by = #{submittedBy}, " +
            "score = #{score}, " +
            "submitted_at = #{submittedAt}, " +
            "is_excellent = #{isExcellent}, " +
            "assignment_id = #{assignmentId}, " +
            "assignment_file = #{assignmentFile}, " +
            "assignment_status = #{assignmentStatus} " +
            "WHERE id = #{id}")
    int update(TeamAssignment teamAssignment);

    @Delete("DELETE FROM team_assignments WHERE id = #{id}")
    int deleteById(Long id);

    // 统计方法
    @Select("SELECT COUNT(*) FROM team_assignments WHERE team_id = #{teamId}")
    int countByTeamId(Long teamId);

    @Select("SELECT COUNT(*) FROM team_assignments WHERE assignment_id = #{assignmentId}")
    int countByAssignmentId(String assignmentId);

    // 业务方法
    @Update("UPDATE team_assignments SET score = #{score}, is_excellent = #{isExcellent} WHERE id = #{id}")
    int updateScoreAndExcellent(@Param("id") Long id, @Param("score") BigDecimal score,
                                @Param("isExcellent") Integer isExcellent);

    @Update("UPDATE team_assignments SET assignment_status = #{status}, assignment_file = #{filePath}, " +
            "submitted_at = NOW() WHERE id = #{id}")
    int updateStatusAndFile(@Param("id") Long id, @Param("status") String status,
                            @Param("filePath") String filePath);

    @Select("SELECT * FROM team_assignments WHERE is_excellent = 1 ORDER BY score DESC")
    @Results({
            @Result(column = "team_id", property = "teamId"),
            @Result(column = "submitted_by", property = "submittedBy"),
            @Result(column = "submitted_at", property = "submittedAt"),
            @Result(column = "created_at", property = "createdAt"),
            @Result(column = "is_excellent", property = "isExcellent"),
            @Result(column = "assignment_id", property = "assignmentId"),
            @Result(column = "assignment_file", property = "assignmentFile"),
            @Result(column = "assignment_status", property = "assignmentStatus")
    })
    List<TeamAssignment> selectExcellentAssignments();

    @Select("SELECT * FROM team_assignments WHERE team_id = #{teamId} AND assignment_id = #{assignmentId}")
    @Results({
            @Result(column = "team_id", property = "teamId"),
            @Result(column = "submitted_by", property = "submittedBy"),
            @Result(column = "submitted_at", property = "submittedAt"),
            @Result(column = "created_at", property = "createdAt"),
            @Result(column = "is_excellent", property = "isExcellent"),
            @Result(column = "assignment_id", property = "assignmentId"),
            @Result(column = "assignment_file", property = "assignmentFile"),
            @Result(column = "assignment_status", property = "assignmentStatus")
    })
    TeamAssignment selectByTeamIdAndAssignmentId(@Param("teamId") Long teamId,
                                                 @Param("assignmentId") String assignmentId);
}